﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CategoryController
/// </summary>
public class CategoryController
{
    private SqlConnection con;
	public CategoryController()
	{
	    con = ConnectDB.Connect();
        ConnectDB.Open();
	}
    public int Insert(Category cat)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Category";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = cat.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = cat.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = cat.Description;
            cmd.Parameters.Add("@tag", SqlDbType.NText).Value = cat.Tag;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = cat.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = cat.Status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = cat.DateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int InsertShowID(Category cat)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Category_Show_ID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = cat.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = cat.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = cat.Description;
            cmd.Parameters.Add("@tag", SqlDbType.NText).Value = cat.Tag;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = cat.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = cat.Status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = cat.DateStart;
            object result= cmd.ExecuteScalar();
            return Convert.ToInt32(result);
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int InsertImageCat(int category_id,string image,int order,int status,DateTime dateStart)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO Category_Image(category_id,[image],[order],[status],dateStart) VALUES(@category_id,@image,@order,@status,@dateStart)";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@category_id", SqlDbType.NVarChar, 50).Value = category_id;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = image;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = dateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int Update(Category cat)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update_Category";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@category_id", SqlDbType.NVarChar, 50).Value = cat.Category_id;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = cat.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = cat.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = cat.Description;
            cmd.Parameters.Add("@tag", SqlDbType.NText).Value = cat.Tag;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = cat.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = cat.Status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = cat.DateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int Delete(int id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete_Category";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@category_id", SqlDbType.NVarChar, 50).Value = id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int deleteImageCatProduct(int category_image_id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "DELETE FROM Category_image WHERE category_image_id=@category_image_id";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@category_image_id", SqlDbType.Int).Value = category_image_id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public DataTable getAllCategory()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Category WHERE [status]=1 ORDER BY [order] ASC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            
            throw;
        }
    }
    public DataTable getEnableCategory()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select distinct Category.name,Category.category_id from Category inner join Product on Category.category_id=Product.category_id AND Product.[top]=1";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
    }
    public bool checkCateId(int cateId) {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Category WHERE category_id = @id AND [status]=1 ORDER BY [order] ASC";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = cateId;
            SqlDataReader dr= cmd.ExecuteReader();
            return dr.HasRows;
        }
        catch (Exception)
        {

            throw;
        }
    }
    public DataTable getCategoryByID(int id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Category WHERE category_id = @id AND [status]=1 ORDER BY [order] ASC";
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
    }
    public List<CategoryImageProduct> getCategoryImageProduct(int category_id)
    {
        List<CategoryImageProduct> list = new List<CategoryImageProduct>();
        try
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "Select_Category_Image_Product";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@category_id", SqlDbType.Int).Value = category_id;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.Connection = con;
            da.Fill(dt);
            foreach (DataRow item in dt.Rows)
            {
                CategoryImageProduct pki = new CategoryImageProduct(item);
                list.Add(pki);
            }
            return list;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
}